create proc [dbo].[p_AverageWeeklyBlockCount](
	@CountOfWeeks int, 
	@DatabaseID bigint) AS  

set nocount on

select
	datepart(ww,a.[CreateDate]) 'iWeek',
	datepart(year,a.[CreateDate]) 'iYear',
	a.SessionID,
	count(distinct a.Blocked_SPID) 'CountOfBlocks' 	
into
	#x 
from  	
	BlockingHistory a  		
where  
	a.[DatabaseID]=@DatabaseID
group by
	datepart(ww,a.[CreateDate]),
	datepart(year,a.[CreateDate]),
	a.SessionID

declare @weeks table(
		dtWeekDate datetime,
		iWeek int,
		iYear int,
		iAverageBlocks int)   
 
declare @iCurWeek int,  		
	@dtCurDate datetime  

set @iCurWeek=0  
while @iCurWeek<@CountOfWeeks  
begin  	
	set @dtCurDate=dateadd(ww,@iCurWeek * -1,current_timestamp)  	

	insert into @weeks(
		dtWeekDate,
		iWeek,
		iYear) values(

		convert(smalldatetime,convert(varchar(25),dateadd(day,1-  datepart(weekday,@dtCurDate),@dtCurDate),107)),
		datepart(ww,@dtCurDate),
		datepart(year,@dtCurDate))   	

	set @iCurWeek=@iCurWeek+1  
end    

select   	
	a.dtWeekDate,
	avg(isnull(b.[CountOfBlocks],0)) 'CountOfBlocks'
from   	
	@weeks a  	
		left outer join #x b on b.iWeek=a.iWeek
				and b.iYear=a.iYear	
group by
	a.dtWeekDate  
order by   	
	a.dtWeekDate    

drop table #x
   

